--exec sp_load_sort_released_lots_for_dataentry 1178, 8

ALTER  PROCEDURE sp_load_sort_released_lots_for_dataentry 
(
@sort_process_step_id INT,
@location_id INT
)
AS
BEGIN
	DECLARE @lot_id INT
	DECLARE @lot_number VARCHAR(50)
	DECLARE @qty_pulled INT
	DECLARE @wafer_ids VARCHAR(100)
	DECLARE @wafer_id VARCHAR(100)
	DECLARE @insert_wafer_id_flag INT
	DECLARE @index INT
	DECLARE @final_wafer_ids VARCHAR(100)
	DECLARE @sort_lot_id BIGINT

	DECLARE @results TABLE ( Lot_Id INT, Lot_Number VARCHAR(50), Qty_Released INT, Wafer_Ids VARCHAR(100) )
	DECLARE  @SORT_RELEASE_CURSOR CURSOR 
	SET @SORT_RELEASE_CURSOR = CURSOR FOR SELECT LOT_ID, LOT_NUMBER FROM SORT_RELEASE_TABLE SRT, SORT_RELEASE_LOT_TABLE SRLT 
				   WHERE SRT.SORT_REL_ID = SRLT.SORT_REL_ID AND PROCESSSTEP_ID = @sort_process_step_id AND STATUS <> 'CANCELLED' AND STATUS <> 'CLOSED'  
				   AND SRT.FROM_LOC_ID = ( SELECT VENDOR_ID FROM LOCATION WHERE LOCATION_ID = @location_id AND L_TYPE = 'Sort' ) GROUP BY LOT_ID,LOT_NUMBER 
	OPEN @SORT_RELEASE_CURSOR
	FETCH NEXT FROM @SORT_RELEASE_CURSOR INTO @lot_id, @lot_number
	WHILE @@FETCh_STATUS = 0
	BEGIN
		SELECT @sort_lot_id =  LOT_ID FROM LOT_TABLE LT WHERE LT.LOT_NUMBER = @lot_number AND PROCESSSTEP_ID = @sort_process_step_id
		SET @final_wafer_ids = ''
		set @qty_pulled = 0
		DECLARE @wafer_cursor CURSOR 
		SET @wafer_cursor = CURSOR FOR SELECT Wafer_Id FROM SORT_RELEASE_TABLE SRT, SORT_RELEASE_LOT_TABLE SRLT  
				    WHERE SRLT.Lot_Id = @lot_id  AND SRT.SORT_REL_ID = SRLT.SORT_REL_ID 
				    AND SRLT.Lot_Id = @lot_id AND STATUS <> 'CANCELLED' AND STATUS <> 'CLOSED'				    

		OPEN @wafer_cursor
		FETCH NEXT FROM @wafer_cursor INTO @wafer_ids
		WHILE @@FETCh_STATUS = 0
		BEGIN
			SET @wafer_id = ''
			SET @index = 0
			--TO CHECK WHTHER THE BINNING INFO IS RECIEVED FOR WAFER ID
			WHILE ( LEN(@wafer_ids) > 0  )
			BEGIN
				SET @insert_wafer_id_flag = 1	--1 MEANS BINNING INFO RCIEVED FOR WAFER ID.				
				IF ( CHARINDEX ( ';', @wafer_ids, 1 ) > 0 ) 
				BEGIN
					SET @index = CHARINDEX ( ';', @wafer_ids, 1 )
					SET @wafer_id = SUBSTRING( @wafer_ids, 0, @index ) 
					SET @wafer_ids = SUBSTRING( @wafer_ids, @index+1, LEN( @wafer_ids ) )

					IF ( ( SELECT COUNT(*) FROM INFORMATIVE_BINNING WHERE Lot_id = @sort_lot_id AND Wafer_Id = @wafer_id ) = 0 )
					BEGIN
						SET @insert_wafer_id_flag = 0				
					END
				END	
				--IF BINNING INFO IS NOT RECIEVED THEN INSERT IN TO RESULTS TABLE
				IF ( @insert_wafer_id_flag = 0 )
				BEGIN
					SET @final_wafer_ids = @final_wafer_ids + CONVERT(VARCHAR,@wafer_id)+';'
					SET @qty_pulled = @qty_pulled + 1	--FOR CALCULATING THE QTY PULLED FOR RELEASE
				END
			END
			
			SET @wafer_ids = ''				
			FETCH NEXT FROM @wafer_cursor INTO @wafer_ids
		END
		
		CLOSE @wafer_cursor 
		DEALLOCATE @wafer_cursor	
		
		INSERT INTO @results ( Lot_Id, Lot_Number, Qty_Released, Wafer_Ids ) VALUES ( @lot_id, @lot_number, @qty_pulled, @final_wafer_ids )
		SET @lot_id = NULL
		SET @lot_number = NULL
		SET @qty_pulled	= NULL		
		SET @sort_lot_id = NULL
		FETCH NEXT FROM @SORT_RELEASE_CURSOR INTO @lot_id, @lot_number
	END
	SELECT * FROM @results ORDER BY Lot_Number
 
END